package com.news.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.news.entity.User;
import com.news.util.DBUtil;

public class UserDaoImpl implements UserDao {

	@Override
	public int insertUser(User user) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		String sql = "insert into sys_user(NAME,ACCT,PWD,CRTIME,UPTIME) values(?,?,?,now(),now())";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, user.getName());
			stmt.setString(2, user.getAcct());
			stmt.setString(3, user.getPwd());
			return stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stmt, null);
		}
		return 0;
	}

	@Override
	public int updateUser(User user) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		String sql = "update sys_user set NAME=?,PWD=?,UPTIME=now() where ID=?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, user.getName());
			stmt.setString(2, user.getPwd());
			stmt.setInt(3, user.getId());
			return stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stmt, null);
		}
		return 0;
	}

	@Override
	public int deleteUser(int id) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		String sql = "delete from sys_user where ID=?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			return stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stmt, null);
		}
		return 0;
	}

	@Override
	public int deleteUserBatch(String[] ids) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		String sql = "delete from sys_user where ID=?";
		try {
			stmt = conn.prepareStatement(sql);
			for (String id : ids) {
				stmt.setInt(1, Integer.parseInt(id));
				//jdbc批量操作，批处理
				stmt.addBatch();
			}
			stmt.executeBatch();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stmt, null);
		}
		return 0;
	}
	
	@Override
	public int deleteUserBatch2(String[] ids) { // [1,2,3,4]
		Connection conn = DBUtil.getConnection();
		//sql拼接
		Statement stmt = null;
		String part_sql = "";
		for (int i = 0; i < ids.length; i++) {
			String id = ids[i];
			part_sql += id;
			if(i != ids.length-1){
				part_sql += ",";
			}
		}
		
		String sql = "delete from sys_user where ID in (" + part_sql + ")";
		System.out.println(sql);
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stmt, null);
		}
		return 0;
	}

	@Override
	public User selectOneUser(int id) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "select ID,NAME,ACCT,PWD,CRTIME,UPTIME from sys_user where ID=?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if(rs.next()){
				User user = new User();
				user.setId(rs.getInt("ID"));
				user.setName(rs.getString("NAME"));
				user.setAcct(rs.getString("ACCT"));
				user.setPwd(rs.getString("PWD"));
				user.setCrtime(rs.getTimestamp("CRTIME"));
				user.setUptime(rs.getTimestamp("UPTIME"));
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stmt, rs);
		}
		return null;
	}

	@Override
	public List<User> selectUser(int offset, int pageSize, String acct) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql1 = "select ID,NAME,ACCT,PWD,CRTIME,UPTIME from sys_user order by ID limit ?,?";
		String sql2 = "select ID,NAME,ACCT,PWD,CRTIME,UPTIME from sys_user where ACCT like concat('%',?,'%') order by ID limit ?,?";
		List<User> list = new ArrayList<User>();
		try {
			if(acct==null || acct.equals("")){
				stmt = conn.prepareStatement(sql1);
				stmt.setInt(1, offset);
				stmt.setInt(2, pageSize);
			}else{
				stmt = conn.prepareStatement(sql2);
				stmt.setString(1, acct);
				stmt.setInt(2, offset);
				stmt.setInt(3, pageSize);
			}
			
			rs = stmt.executeQuery();
			while(rs.next()){
				User user = new User();
				user.setId(rs.getInt("ID"));
				user.setName(rs.getString("NAME"));
				user.setAcct(rs.getString("ACCT"));
				user.setPwd(rs.getString("PWD"));
				user.setCrtime(rs.getTimestamp("CRTIME"));
				user.setUptime(rs.getTimestamp("UPTIME"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stmt, rs);
		}
		return list;
	}

	@Override
	public int selectCount(String acct) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql1 = "select count(*) from sys_user";
		String sql2 = "select count(*) from sys_user where ACCT like concat('%',?,'%')";
		try {
			if(acct==null || acct.equals("")){
				stmt = conn.prepareStatement(sql1);
			}else{
				stmt = conn.prepareStatement(sql2);
				stmt.setString(1, acct);
			}
			
			rs = stmt.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stmt, rs);
		}
		return 0;
	}

	@Override
	public User login(String acct, String pwd) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = "select ID,NAME,ACCT,PWD,CRTIME,UPTIME from sys_user where ACCT=? and PWD=?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, acct);
			stmt.setString(2, pwd);
			rs = stmt.executeQuery();
			if(rs.next()){
				User user = new User();
				user.setId(rs.getInt("ID"));
				user.setName(rs.getString("NAME"));
				user.setAcct(rs.getString("ACCT"));
				user.setPwd(rs.getString("PWD"));
				user.setCrtime(rs.getTimestamp("CRTIME"));
				user.setUptime(rs.getTimestamp("UPTIME"));
				return user;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, stmt, rs);
		}
		return null;
	}

}
